#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : byxmltosql.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/6/24 11:26                 #
#                                                                   #
#                   @Last Update  : 2021/6/24 11:26                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:北京标引数据入库                                                          #
#                                                                   #
#-------------------------------------------------------------------#
"""
import os
import re
from shutil import copyfile
from xml.dom import minidom

import pymysql

from Seetings import JGMYSQL_CONNEXT

class BYXMLTOSQL:

    def __init__(self):
        self.node_list_dict = {"`law`": ["LegalName", "Organizaation", "PublishDate", "EffectiveDate", "Keyword", "SortA", "SortB", "SortC", "SortD", "SortE", "SortF", "SortG", "pageNum"],
                                "`case`": ["Title", "People", "CaseOfAction", "UseLaw", "JudgAgency", "CaseSummary", "CaseNumber", "AdjudicationDate", "Keyword", "pageNum"],
                                "`treaty`": ["Title", "StateParty"]}
        self.node_field = {"PublishDate": "PublishDateStr", "EffectiveDate": "EffectiveDateStr", "SortE": "chapNo", "pageNum": "PageNum", "AdjudicationDate": "AdjudicationDateStr"}
        # 加工数据库连接
        self.jg_conn = pymysql.connect(host=JGMYSQL_CONNEXT["MYSQL_HOST"],
                                  port=JGMYSQL_CONNEXT["MYSQL_PORT"],
                                  db=JGMYSQL_CONNEXT["MYSQL_DB"],
                                  user=JGMYSQL_CONNEXT["MYSQL_USER"],
                                  passwd=JGMYSQL_CONNEXT["MYSQL_PASSWD"],
                                  charset=JGMYSQL_CONNEXT["MYSQL_CHARSET"])

    def __del__(self):
        self.jg_conn.close()

    def __reConnect(self):
        """
        查看数据库连接是否有效
        :return:
        """
        try:
            self.jg_conn.ping()
        except:
            self.jg_conn()

    def __writesql(self, table, file_name, valuedict: dict, update_status):
        """
        将读取的xml数据入库
        :param table: 数据库待更新表名
        :param file_name:  xml文件名
        :param valuedict: 入库内容
        :param lang_suf: 入库语种后缀
        :param lang_sym: 翻译结果标志
        :param update_status: 是否需要更新数据状态
        :return:
        """
        self.__reConnect()  # 检查数据库连接是否有效
        SYS_FLD_DIGITFILENAME = re.sub('''\.xml$''', '''.pdf''', file_name)
        # sysid = file_name.rstrip("%s.xml"%lang_sym)

        value_list = []  # 更新数据字段的字符串
        field_list = []     # 需要更新的字段列表

        for key in valuedict.keys():
            field_list.append(key)
        field_list_str = ','.join(field_list)
        sql = '''select %s from %s where SYS_FLD_DIGITFILENAME = "%s"''' % (field_list_str,table,SYS_FLD_DIGITFILENAME)
        try:
            with self.jg_conn.cursor() as jg_cursor:
                jg_cursor.execute(sql)
                field_values = jg_cursor.fetchall()
        except Exception as e:
            print("加工服务器查询数据值：%s(%s)" % (str(e), sql))
            return False
        # 获取入库字段和入库字段值
        if len(field_values):
            for i in range(len(field_list)):
                if field_values[0][i] != "" and field_values[0][i] != "NULL" and field_values[0][
                    i] != None:  # 若数据库中原值不为空则使用数据库中的值，否则使用北京标引的值
                    field_value = field_values[0][i]
                else:
                    field_value = valuedict[field_list[i]]
                if type(field_value) == str:
                    field_value = pymysql.escape_string(field_value)
                value_list.append('''%s="%s"''' % (field_list[i], field_value))
            value_list_str = ','.join(value_list)
            if update_status == 1:  # 更新数据状态
                value_list_str = value_list_str + ''',SYS_FLD_MARKSTATE = "999"'''
            sql = '''update %s set %s where SYS_FLD_DIGITFILENAME = "%s"''' % (
            table, value_list_str, SYS_FLD_DIGITFILENAME)
            print(sql)
            try:
                with self.jg_conn.cursor() as jg_cursor:
                    jg_cursor.execute(sql)
                    self.jg_conn.commit()
            except Exception as e:
                print("加工服务器更新数据出错：%s(%s)" % (str(e), sql))
                return False
            return True
        else:
            print("该条数据在数据库中不存在：%s" % SYS_FLD_DIGITFILENAME)
            return False


    def __readXML(self, table, input_path, update_status):
        """
        解析xml文件
        :param table:数据库表名
        :param input_path:文件路径
        :param update_status: 是否需要更新数据状态
        :return: 一个值的字典
        """
        # 数据库表对应的字段名

        node_list = self.node_list_dict[table]
        file = os.path.split(input_path)[1]
        # 解析xml文件
        try:
            domtree = minidom.parse(input_path)
            # 获取根节点
            datalist = domtree.documentElement
        except Exception as e:
            print("解析%sxml文件出错:%s" % (input_path, str(e)))
            return False
        # 获取标签子节点的值并存为字典
        resultdict = {}
        for node in node_list:
            if datalist.getElementsByTagName(node):
                if datalist.getElementsByTagName(node)[0].childNodes:
                    if node in self.node_field.keys():
                        resultdict[self.node_field[node]] = datalist.getElementsByTagName(node)[0].childNodes[0].data
                    else:
                        resultdict[node] = datalist.getElementsByTagName(node)[0].childNodes[0].data
        result = self.__writesql(table,file,resultdict,update_status)
        return result

    def importdate(self):
        """
        xml北京标引完毕文件导入
        :return:
        """
        # 输入标引完毕xml文件路径
        file_root = input("please input import file path: ")
        try:
            file_root_path = os.path.join(file_root)
        except:
            print("input erro")
            return None
        # 输入入库表
        table_num = input("选择入库表(法律:0; 判例:1; 国际条约:2)")
        table_dict = {"0": "`law`", "1": "`case`", "2": "`treaty`"}
        try:
            table = table_dict[table_num]
        except Exception as e:
            print("输入表超出范围:%s" % str(e))
            return None
        # # 输入入库语种
        # lang_num = input("选择入库语言(中文:0; 英文:1; 小语种:2):")
        # # 入库语种后缀
        # lang_suf_dict = {"0":"C", "1":"E", "2":"X"}
        # try:
        #     lang_suf = lang_suf_dict[lang_num]
        # except Exception as e:
        #     print("输入语言超出范围:%s" % str(e))
        #     return None
        # # 输翻译结果后缀
        # lang_sym_num = input("选择翻译结果标志(无:0; e:1; c:2):")
        # lang_sym_dict = {"0":"", "1":"e", "2":"c"}
        # try:
        #     lang_sym = lang_sym_dict[lang_sym_num]
        # except Exception as e:
        #     print("输入标志超出范围:%s" % str(e))
        #     return None
        # 是否更新数据状态
        update_status_num = input("是否更新数据状态(不更新:0; 更新:1)")
        try:
            update_status = int(update_status_num)
        except:
            update_status = 0
        # 将每篇翻译完成的xml导入sql
        for file in os.listdir(file_root_path):
            file_path = os.path.join(file_root_path, file)
            # xml文件导入sql
            result = self.__readXML(table, file_path, update_status)
            if not result:
                # 导入错误数据存入错误文件夹
                if not os.path.exists(file_root_path + '\\erro'):
                    os.mkdir(file_root_path + '\\erro')
                copyfile(file_path, file_root_path + '\\erro\\' + file)


if __name__ == '__main__':
    BYXMLTOSQL().importdate()